from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

try:
    # 连接mysql
    MysqlEngine = create_engine('mysql+pymysql://root:mysql@127.0.0.1:3306/study?charset=utf8')

    print('连接mysql数据库成功', MysqlEngine)
except Exception as e:
    print('连接数据库失败!', e)


# 创建基类
Base = declarative_base()


# 定义学生对象
class Today(Base):
    # 表的名字
    __tablename__ = 'Today'
    # 字段
    sno = Column(String(2), primary_key=True)  # 学号
    sname = Column(String(20))  # 姓名
    age = Column(Integer)
    # 创建表的参数
    __table_args__ = {
        'mysql_charset': 'utf8'
    }

    # 查询的时候显示的是值，而不是内存地址
    def __repr__(self):
        return "Today name:{},age:{}".format(self.sname, self.age)


# 创建与数据库进行会话
MysqlSession = sessionmaker(bind= MysqlEngine)
# 创建session对象
session = MysqlSession()
# 查询姓名为张三的学生
stu = session.query(Today).filter(Today.sname == '张三')
# 查询所有数据
stu_all = session.query(Today).all()
print('查询结果的数据类型:',type(stu_all))
for i in stu_all:
    print(i)
session.close()